﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using iAnywhere.Data.SQLAnywhere;
using Oracle.DataAccess.Client;
using System.Configuration;
using System.Globalization;
using System.Xml;
using System.Xml.Serialization;
using System.IO;
using T_Performance.Service;
using T_Performance.Model;

namespace T_Performance
{
    public class Program
    {
        static Dictionary<string, int> companyDic = new Dictionary<string, int>();
        static DateTime timestamp;
        static bool hasTimeStamp = false;
        static Dictionary<string, int> bsDic = new Dictionary<string, int>();
        static string itPortal = ConfigurationManager.AppSettings["ItPortal"].ToString();

        public static void Main(string[] args)
        {

            SysLog.OpenStream();

            #region //清空Bmmp中T_Performance表中的临时数据
            bool clearStatus = Convert.ToBoolean(ConfigurationManager.AppSettings["ClearStatus"]);
            if (clearStatus)
            {
                //ClearTempBppmData.ClearDataByTime();
                //ClearTempBppmData.ClearT_PerformanceByTime(itPortal);
            }
            #endregion

            long fromtime = 0;
            long totime = 0;
            try
            {
                //获取格林尼治时间
                gmtTimeEntity gte = GetGMTDateTime();
                fromtime = gte.fromtime;
                totime = gte.totime;
            }
            catch (Exception ex)
            {
                SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                return;
            }
            try
            {
                SysLog.WriteSysLog("Message write Time:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                var path = "E:\\Program\\t_Performance\\sql.xml";
                //读取 xml文件
                string xmlsql = File.ReadAllText(path);
                root objroot = XmlDeserialize<root>(xmlsql);

                #region  //省局
                //获取福建省局的cleardevice的sql语句
                string sheng_device_Clr_Str = objroot.sheng.device.delete;
                //获取福建省局的loaddevice的sql语句
                string sheng_device_load_Str = objroot.sheng.device.load;
                //获取福建省局的loadperformance语句数组
                String[] sheng_performance_load_Arr = objroot.sheng.performance.load;
                //获取福建省局的loadtmartavai的sql语句
                //string sheng_tmartavai_load_Str = objroot.sheng.tmartavai.load;

                #endregion

                #region //厦门
                //获取厦门地区的cleardevice的sql语句
                string xm_device_Clr_Str = objroot.xiamen.device.delete;
                //获取厦门地区的loaddevice的sql语句
                string xm_device_load_Str = objroot.xiamen.device.load;
                //获取厦门地区的loadperformance语句数组
                String[] xm_performance_load_Arr = objroot.xiamen.performance.load;
                //获取厦门地区的loadtmartavai的sql语句
                //string xm_tmartavai_load_Str = objroot.xiamen.tmartavai.load;

                #endregion

                #region  //福州
                //获取福州地区的cleardevice的sql语句
                string fz_device_Clr_Str = objroot.fuzhou.device.delete;
                //获取福州地区的loaddevice的sql语句
                string fz_device_load_Str = objroot.fuzhou.device.load;
                //获取福州地区的loadperformance语句数组
                String[] fz_performance_load_Arr = objroot.fuzhou.performance.load;
                //获取福州地区的loadtmartavai的sql语句
                //string fz_tmartavai_load_Str = objroot.fuzhou.tmartavai.load;

                #endregion

                #region  //莆田
                //获取莆田地区的cleardevice的sql语句
                string pT_Device_Clr_Str = objroot.putian.device.delete;
                //获取莆田地区的loaddevice的sql语句
                string pT_Device_load_Str = objroot.putian.device.load;
                //获取莆田地区的loadperformance语句数组
                String[] pT_Performance_load_Arr = objroot.putian.performance.load;
                //获取莆田地区的loadtmartavai的sql语句
                //string pt_tmartavai_load_Str = objroot.putian.tmartavai.load;

                #endregion

                #region  //泉州
                //获取泉州地区的cleardevice的sql语句
                string qz_device_Clr_Str = objroot.quanzhou.device.delete;
                //获取泉州地区的loaddevice的sql语句
                string qz_device_load_Str = objroot.quanzhou.device.load;
                //获取泉州地区的loadperformance语句数组
                String[] qz_performance_load_Arr = objroot.quanzhou.performance.load;
                //获取泉州地区的loadtmartavai的sql语句
                //string qz_tmartavai_load_Str = objroot.quanzhou.tmartavai.load;

                #endregion

                #region  //漳州

                //获取漳州地区的cleardevice的sql语句
                string zz_device_Clr_Str = objroot.zhangzhou.device.delete;
                //获取漳州地区的loaddevice的sql语句
                string zz_device_load_Str = objroot.zhangzhou.device.load;
                //获取漳州地区的loadperformance语句数组
                String[] zz_performance_load_Arr = objroot.zhangzhou.performance.load;
                //获取漳州地区的loadtmartavai的sql语句
                //string zz_tmartavai_load_Str = objroot.zhangzhou.tmartavai.load;

                #endregion

                #region  //南平
                //获取南平地区的cleardevice的sql语句
                string np_device_Clr_Str = objroot.nanping.device.delete;
                //获取南平地区的loaddevice的sql语句
                string np_device_load_Str = objroot.nanping.device.load;
                //获取南平地区的loadperformance语句数组
                String[] np_performance_load_Arr = objroot.nanping.performance.load;
                //获取南平地区的loadtmartavai的sql语句
                //string np_tmartavai_load_Str = objroot.nanping.tmartavai.load;

                #endregion

                #region  //龙岩
                //获取龙岩地区的cleardevice的sql语句
                string ly_device_Clr_Str = objroot.longyan.device.delete;
                //获取龙岩地区的loaddevice的sql语句
                string ly_device_load_Str = objroot.longyan.device.load;
                //获取龙岩地区的loadperformance语句数组
                String[] ly_performance_load_Arr = objroot.longyan.performance.load;
                //获取龙岩地区的loadtmartavai的sql语句
                //string ly_tmartavai_load_Str = objroot.longyan.tmartavai.load;

                #endregion

                #region  //宁德
                //获取宁德地区的cleardevice的sql语句
                string nd_device_Clr_Str = objroot.ningde.device.delete;
                //获取宁德地区的loaddevice的sql语句
                string nd_device_load_Str = objroot.ningde.device.load;
                //获取宁德地区的loadperformance语句数组
                String[] nd_performance_load_Arr = objroot.ningde.performance.load;
                //获取宁德地区的loadtmartavai的sql语句
                //string nd_tmartavai_load_Str = objroot.ningde.tmartavai.load;

                #endregion

                #region  //三明
                //获取三明地区的cleardevice的sql语句
                string sm_device_Clr_Str = objroot.sanming.device.delete;
                //获取三明地区的loaddevice的sql语句
                string sm_device_load_Str = objroot.sanming.device.load;
                //获取三明地区的loadperformance语句数组
                String[] sm_performance_load_Arr = objroot.sanming.performance.load;
                //获取三明地区的loadtmartavai的sql语句
                //string sm_tmartavai_load_Str = objroot.sanming.tmartavai.load;

                #endregion

                #region  //武夷烟叶
                /*
                    //获取武夷烟叶地区的cleardevice的sql语句
                    string wyyy_device_Clr_Str = objroot.wuyiyanye.device.delete;
                    //获取武夷烟叶地区的loaddevice的sql语句
                    string wyyy_device_load_Str = objroot.wuyiyanye.device.load;
                    //获取武夷烟叶地区的loadperformance语句数组
                    String[] wyyy_performance_load_Arr = objroot.wuyiyanye.performance.load;
                    //获取武夷烟叶地区的loadtmartavai的sql语句
                    //string wyyy_tmartavai_load_Str = objroot.wuyiyanye.tmartavai.load;
                    */
                #endregion

                #region  //金叶复烤
                /*
                    //获取金叶复烤地区的cleardevice的sql语句
                    string jyfk_device_Clr_Str = objroot.jinyefukao.device.delete;
                    //获取金叶复烤地区的loaddevice的sql语句
                    string jyfk_device_load_Str = objroot.jinyefukao.device.load;
                    //获取金叶复烤地区的loadperformance语句数组
                    String[] jyfk_performance_load_Arr = objroot.jinyefukao.performance.load;
                    //获取金叶复烤地区的loadtmartavai的sql语句
                    //string jyfk_tmartavai_load_Str = objroot.jinyefukao.tmartavai.load;
                    */
                #endregion



                string bppmConn = string.Empty;
                foreach (var bppmConnKey in ConfigurationManager.AppSettings.Keys)
                {
                    #region //福建省烟草专卖局
                    if (bppmConnKey.ToString() == "福建省烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, sheng_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, sheng_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //三明市烟草专卖局
                    else if (bppmConnKey.ToString() == "三明市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, sm_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, sm_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //宁德市烟草专卖局
                    else if (bppmConnKey.ToString() == "宁德市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, nd_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, nd_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //南平市烟草专卖局
                    else if (bppmConnKey.ToString() == "南平市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, np_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, np_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //厦门市烟草专卖局
                    else if (bppmConnKey.ToString() == "厦门市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, xm_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, xm_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //福州市烟草专卖局
                    else if (bppmConnKey.ToString() == "福州市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, fz_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, fz_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //龙岩市烟草专卖局
                    else if (bppmConnKey.ToString() == "龙岩市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, ly_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, ly_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //漳州市烟草专卖局
                    else if (bppmConnKey.ToString() == "漳州市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, zz_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, zz_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //泉州市烟草专卖局
                    else if (bppmConnKey.ToString() == "泉州市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, qz_device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, qz_performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion
                    #region //莆田市烟草专卖局
                    else if (bppmConnKey.ToString() == "莆田市烟草专卖局")
                    {
                        bppmConn = ConfigurationManager.AppSettings[bppmConnKey.ToString()];
                        SysLog.WriteSysLog("---------" + bppmConnKey.ToString() + "---------");
                        try
                        {
                            //Sybase -> T_Performance
                            ClearT_DeviceTable();
                            LoadT_DeviceTable(bppmConn, pT_Device_load_Str);
                            ClearT_PerformanceTable();
                            LoadT_PerformanceTable(bppmConnKey.ToString(), fromtime, totime, bppmConn, pT_Performance_load_Arr);
                            //T_Performance -> Performance
                            SyncPerformance(bppmConn, bppmConnKey.ToString());
                            
                        }
                        catch (Exception ex)
                        {
                            SysLog.WriteSysLog(bppmConnKey.ToString() + ":" + bppmConn);
                            SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                            continue;
                        }
                    }
                    #endregion

                }

                SysLog.WriteSysLog("--------------------------------end---------------------------------");
            }
            catch (Exception ex)
            {
                SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                return;
            }

            SysLog.CloseStream();
        }

        public static gmtTimeEntity GetGMTDateTime()
        {
            SysLog.WriteSysLog("begin to get GMT time ...................");
            int IDate = Convert.ToInt32(ConfigurationManager.AppSettings["LastDate"]);
            DateTime lastDate = DateTime.Now.AddDays(IDate);
            DateTime currentDate = DateTime.Now;

            //DateTime lastDate = DateTime.Now.AddDays(-2);
            //DateTime currentDate = DateTime.Now.AddDays(-1);

            string StrLastDate = lastDate.ToShortDateString().ToString();
            string StrCurrentDate = currentDate.ToShortDateString().ToString();

            string StrTimeFrom = StrLastDate + " 00:00:00";
            string StrTimeTo = StrCurrentDate + " 00:00:00";

            DateTime DtTimeFrom = DateTime.Parse(StrTimeFrom);
            DateTime DtTimeTo = DateTime.Parse(StrTimeTo);

            SysLog.WriteSysLog("start time for calc is " + DtTimeFrom.ToString());
            SysLog.WriteSysLog("end time for calc is " + DtTimeTo.ToString());

            long fromTime = (DtTimeFrom.Ticks - 621355968000000000) / 10000000 - 28800;
            long toTime = (DtTimeTo.Ticks - 621355968000000000) / 10000000 - 28800;

            SysLog.WriteSysLog("GMT Start Time is " + fromTime.ToString());
            SysLog.WriteSysLog("GMT End Time is " + toTime.ToString());

            return new gmtTimeEntity
            {
                fromtime = fromTime,
                totime = toTime

            };

        }

        #region //Sybase中到T_Performance
        public static void ClearT_DeviceTable() 
        {
            string sql = "delete from T_Device";
            using (OracleConnection conn = new OracleConnection(itPortal)) 
            {
                conn.Open();
                SysLog.WriteSysLog("Clear T_Device Table!");
                OracleCommand cmd = new OracleCommand(sql, conn);
                int ret = cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
        }

        public static void ClearT_PerformanceTable()
        {
            string sql = "delete from T_Performance";
            using (OracleConnection conn = new OracleConnection(itPortal))
            {
                conn.Open();
                SysLog.WriteSysLog("Clear T_Performance Table!");
                OracleCommand cmd = new OracleCommand(sql, conn);
                int ret = cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
        }

        public static void LoadT_DeviceTable(string bppmStr, string sql) 
        {

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();
                SACommand cmd = new SACommand("delete from t_device", conn);
                int rd = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }

            List<TDevice> TDevices = new List<TDevice>();
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();
                SACommand cmd = new SACommand(sql, conn);
                int rd = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }

            using (SAConnection conn = new SAConnection(bppmStr)) 
            {
                conn.Open();
                SACommand cmd = new SACommand("select * from t_device", conn);
                SADataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                   TDevices.Add(new TDevice()
                    {
                        MoinstID = Convert.ToInt32(rd["moinstid"].ToString()),
                        DeviceName = rd["devicename"].ToString(),
                        Type = rd["type"].ToString(),
                        Object = rd["object"].ToString(),
                        InstName = rd["instname"].ToString(),
                        CreateTime = DateTime.Now
                    });

                }
                EnsureSACmdAndReader(cmd, rd);
                conn.Close();
            }

            SysLog.WriteSysLog("TDevices.Count: " + TDevices.Count);

            using (OracleConnection conn = new OracleConnection(itPortal)) 
            {
                conn.Open();
                foreach (var item in TDevices)
                {
                    string mysql = "insert into T_Device values ("
                            + item.MoinstID + ", '"
                            + item.DeviceName + "', '"
                            + item.Type + "', '"
                            + item.Object + "', '"
                            + item.InstName + "', to_date('"
                            + item.CreateTime.ToString("yyyy/MM/dd") + "','yyyy/MM/dd'))";

                    //SysLog.WriteSysLog(mysql);

                    OracleCommand cmd = new OracleCommand(mysql, conn);
                    int i = cmd.ExecuteNonQuery();
                    cmd.Dispose();

                }
                conn.Close();
            }



        }

        public static void LoadT_PerformanceTable(string company, long fromTime, long toTime, string bppmStr, String[] sqlArr) 
        {
            int n = 0;
            List<ndPerformanceEntity> ndpe = new List<ndPerformanceEntity>();
            SysLog.WriteSysLog("---------------Start to : " + company + "---------------");

            using (OracleConnection conn = new OracleConnection(itPortal))
            {
                #region //计算ID
                conn.Open();
                string id_sql = @"select max(ID) from T_Performance";
                try
                {
                    OracleCommand cmd1 = new OracleCommand(id_sql, conn);
                    OracleDataReader rd1 = cmd1.ExecuteReader();

                    while (rd1.Read())
                    {
                        if (rd1[0] is DBNull) n = 0;
                        else
                            n = Convert.ToInt32(rd1[0]);
                    }
                    EnsureORCmdAndReader(cmd1, rd1);
                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                }
                conn.Close();
                #endregion
            }

            #region //将数据注入到List中

            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                foreach (var sql in sqlArr)
                {
                    string sqlStr = FilterSQL(sql, fromTime, toTime);
                    SACommand cmd = new SACommand(sqlStr, conn);
                    SADataReader rd = cmd.ExecuteReader();

                    while (rd.Read())
                    {
                        ndpe.Add(new ndPerformanceEntity
                        {
                            Moinstid = rd["moinstid"].ToString(),
                            Identify = rd["devicename"].ToString(),
                            Parameter = rd["Parameter"].ToString(),
                            ParameterDisplay = rd["ParameterDisplay"].ToString(),
                            value = rd["value"].ToString(),
                            CreateTime = (DateTime)rd["CreateTime"],
                            FromTime = rd["fromtime"].ToString(),
                            ToTime = rd["totime"].ToString(),
                        });
                    }

                    EnsureSACmdAndReader(cmd, rd);
                }
                conn.Close();
            }
            #endregion

            SysLog.WriteSysLog("------------ : " + company + "---------------ndpe.count: "+ndpe.Count);

            #region //将list中的数据插入到T_Performance中
            using (OracleConnection conn = new OracleConnection(itPortal))
            {
                conn.Open();
                foreach (var performance in ndpe)
                {
                    string mysql = null;
                    try
                    {
                        string moinstidStr = performance.Moinstid;
                        string ipStr = performance.Identify;
                        string ParameterStr = performance.Parameter;
                        string ParameterDisplayStr = performance.ParameterDisplay;
                        string valueStr = performance.value;
                        DateTime createtimeDt = performance.CreateTime;
                        string fromtimeStr = performance.FromTime;
                        string totimeStr = performance.ToTime;
                        DateTime createTime = DateTime.Now;

                        //string mysql = String.Format("insert into T_Performance values ({0},'{1}','{2}','{3}','{4}','{5}',getdate(),'{6}','{7}')", n++, moinstidStr, ipStr, ParameterStr, ParameterDisplayStr, valueStr, fromtimeStr, totimeStr);

                        mysql = "insert into T_Performance values ("
                            + n++ + ", '"
                            + moinstidStr + "', '"
                            + ipStr + "', '"
                            + ParameterStr + "', '"
                            + ParameterDisplayStr + "', '"
                            + valueStr + "',sysdate, '"
                            + fromtimeStr + "', '"
                            + totimeStr + "')";

                        OracleCommand cmd = new OracleCommand(mysql, conn);
                        //if (company == "福建省烟草专卖局")
                        //    SysLog.WriteSysLog(mysql);
                        int i = cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(mysql);
                        SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                        continue;
                    }
                }
                conn.Close();
            }
            #endregion
        }
        #endregion

        #region //T_Performance -> Performance
        public static void SyncPerformance(string bppmConn, string bppmConnKey) 
        {
            companyDic.Clear();
            timestamp = Convert.ToDateTime("1970-1-1 0:0:0");
            hasTimeStamp = false;
            SysLog.WriteSysLog("Begin to T_Performance -> Performance...");

            using (OracleDataAccess oc = new OracleDataAccess())
            {
                //open oracel
                oc.Open();

                //init company dic
                string sql0 = @"select id,name from sitebranch";
                OracleDataReader reader = null;
                try
                {
                    reader = oc.ExecuteReader(sql0);
                    while (reader.Read())
                    {
                        companyDic.Add(reader["name"].ToString(), Convert.ToInt32(reader["id"]));
                    }
                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog("Init site failed." + ex.ToString() +ex.StackTrace);
                }
                finally
                {
                    reader.Close();
                }

                //init bs
                string sql = @"select b.id as id,b.name as bname,s.name as cname from BUSINESSSYSTEM b inner join sitebranch s on b.siteid = s.id";
                try
                {
                    reader = oc.ExecuteReader(sql);
                    while (reader.Read())
                    {
                        if (!bsDic.ContainsKey(reader["bname"].ToString() + reader["cname"].ToString()))
                            bsDic.Add(reader["bname"].ToString() + reader["cname"].ToString(), Convert.ToInt32(reader["id"]));
                    }
                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog("Init businessSystem failed." + ex.ToString() +ex.StackTrace);
                }
                finally
                {
                    reader.Close();
                }


                if (string.IsNullOrEmpty(bppmConn)) //skip empty value
                {
                    SysLog.WriteSysLog("The value of " + bppmConnKey + " is empty, skip it.");
                }
                else
                {

                    try
                    {
                        //import
                        Import(oc, bppmConnKey);
                        //ImportBs(oc, bppmConn, bppmConnKey);
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                    }
                }

            }
        }

        //oda is already open
        //bppmStr is bppm connection string
        //key is company name
        static void Import(OracleDataAccess oda, string key)
        {
            string sql = string.Empty;
            /////开始导入Device
            //查询所有的Deivce，服务器会聚合，其他不聚合
            sql = @"select min(MOINSTID) as Mid,DeviceName,Type,Object,Instname as Instname from T_Device where type='服务器'
                      group by DeviceName,Type,Object,Instname
                      union
                      select min(MOINSTID) as Mid,DeviceName,Type,Object,Instname from T_Device where type != '服务器'
                      group by DeviceName,Type,Object,Instname";

            OracleDataReader rd = oda.ExecuteReader(sql);
            List<DeviceFromBPPM> devices = new List<DeviceFromBPPM>();

            while (rd.Read())
            {
                devices.Add(new DeviceFromBPPM
                {
                    Mid = (int)((Decimal)rd["Mid"]),
                    Identify = rd["DeviceName"].ToString(),
                    Type = rd["Type"].ToString(),
                    Object = rd["Object"].ToString(),
                    SubObject = rd["Instname"].ToString()
                });
            }

            //查询oracle中的数据进行同步
            //遍历devices，看oracle中有没有记录，没有的话就添加，否则不做动作
            foreach (var device in devices)
            {
                try
                {
                    sql = "select id from Device where";
                    if (string.IsNullOrEmpty(device.Identify))
                        sql += " identify is null";
                    else
                        sql += " identify='" + device.Identify + "'";
                    if (string.IsNullOrEmpty(device.Type))
                        sql += " and type is null";
                    else
                        sql += " and type='" + device.Type + "'";
                    if (string.IsNullOrEmpty(device.Object))
                        sql += " and object is null";
                    else
                        sql += " and object='" + device.Object + "'";
                    if (string.IsNullOrEmpty(device.SubObject))
                        sql += " and subobject is null";
                    else
                        sql += " and subobject='" + device.SubObject + "'";

                    if (oda.ExecuteScalar(sql) == null) //没有对应记录，需要添加
                    {
                        OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("bppmid",null),
                                new OracleParameter("siteid",companyDic[key]),
                                new OracleParameter("bsid",null),
                                new OracleParameter("identify",device.Identify),
                                new OracleParameter("type",device.Type),
                                new OracleParameter("object",device.Object),
                                new OracleParameter("subobject",device.SubObject),
                                new OracleParameter("createtime",DateTime.Now),
                            };

                        oda.ExecuteCommand("insert into device values(:id,:bppmid,:siteid,:bsid,:identify,:type,:object,:subobject,:createtime)", param);
                        SysLog.WriteSysLog("One Device Inserted.");
                    }
                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                }

            }

            /////----------------------------------开始导入performance

            //读取oracle库中所有该地市的device，并做成字典
            //key : identify+type+object+subobject
            Dictionary<string, int> deviceInOracle = new Dictionary<string, int>();
            int companyId = companyDic[key];

           // SysLog.WriteSysLog("11111111        " +companyId);

            sql = @"select id,identify,type,object,subobject from device where siteid=" + companyId;

            var deviceReader = oda.ExecuteReader(sql);
            while (deviceReader.Read())
            {
                string key3 = deviceReader["identify"].ToString() + deviceReader["type"].ToString() + deviceReader["object"].ToString() + deviceReader["subobject"].ToString();
                if (deviceInOracle.ContainsKey(key3))
                    continue;
                deviceInOracle.Add(key3, Convert.ToInt32(deviceReader["id"]));
            }

            deviceReader.Close();

            //SysLog.WriteSysLog("22222");

            //读取sybase中的性能数据，并遍历,
            //对于每条性能数据，在oracle中找，如果找到，则不处理 ，如果找不到则插入performace表
            sql = @"select p.IP,d.Type,d.Object,d.Instname,p.Parameter,(p.ParameterDisplay || '-' || d.Instname) as ParameterDisplay,p.value,p.fromTime as timestamp
                      from T_Performance p
                      inner join T_Device d on d.MOINSTID = p.DeviceId";

            OracleDataReader rd2 = oda.ExecuteReader(sql);

            //SysLog.WriteSysLog("44444444");

            while (rd2.Read())
            {
                try
                {

                   // SysLog.WriteSysLog("33333333 ");


                    string sql3 = null;
                    List<OracleParameter> OracleParameters = new List<OracleParameter>();
                    //TODO：服务器不需要判别subobject
                    if (rd2["Type"].ToString() == "服务器")
                    {
                        sql3 = string.Format(@"select 
--d.Id,d.Identify,d.Type,d.Object,d.SubObject,p.ParameterDisplay,p.Timestamp
p.Id
from performance p
inner join device d on p.deviceid = d.Id
where d.Identify=:identify and d.Type=:type and d.Object=:object and p.ParameterDisplay=:parameterdisplay and p.Timestamp=to_date('{0}','mm/dd/yyyy hh24:mi:ss')", (new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)).ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo));

                        OracleParameters.Add(new OracleParameter("identify", rd2["IP"]));
                        OracleParameters.Add(new OracleParameter("type", rd2["Type"]));
                        OracleParameters.Add(new OracleParameter("object", rd2["Object"]));
                        OracleParameters.Add(new OracleParameter("parameterdisplay", rd2["ParameterDisplay"]));
                        //OracleParameters.Add(new OracleParameter("timestamp", ));
                    }
                    else
                    {
                        sql3 = string.Format(@"select 
--d.Id,d.Identify,d.Type,d.Object,d.SubObject,p.ParameterDisplay,p.Timestamp
p.Id
from performance p
inner join device d on p.deviceid = d.Id
where d.Identify=:identify and d.Type=:type and d.Object=:object and d.SubObject=:subobject and p.ParameterDisplay=:parameterdisplay and p.Timestamp=to_date('{0}','mm/dd/yyyy hh24:mi:ss')", (new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)).ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo));


                        OracleParameters.Add(new OracleParameter("identify", rd2["IP"]));
                        OracleParameters.Add(new OracleParameter("type", rd2["Type"]));
                        OracleParameters.Add(new OracleParameter("object", rd2["Object"]));
                        OracleParameters.Add(new OracleParameter("subobject", rd2["Instname"]));
                        OracleParameters.Add(new OracleParameter("parameterdisplay", rd2["ParameterDisplay"]));
                        //OracleParameters.Add(new OracleParameter("timestamp", rd2["timestamp"]));
                    }

                    if (oda.ExecuteScalar(sql3, OracleParameters.ToArray()) == null) //没有对应记录，需要添加
                    {
                        string key2 = null;
                        //从deviceInOracle字典里检索deivceid
                        //TODO：服务器不需要判别subobject
                        if (rd2["Type"].ToString() == "服务器")
                        {
                            key2 = rd2["IP"].ToString() + rd2["Type"].ToString() + rd2["Object"].ToString();
                        }
                        else
                        {
                            key2 = rd2["IP"].ToString() + rd2["Type"].ToString() + rd2["Object"].ToString() + rd2["Instname"].ToString();
                        }
                        if (!deviceInOracle.ContainsKey(key2))
                        {
                            SysLog.WriteSysLog(key2 + " can not match device.");
                        }
                        PerformanceValue value = ConvertValue(rd2["Parameter"], rd2["value"]);

                        OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("deviceid",deviceInOracle[key2]),
                                new OracleParameter("parameter",rd2["Parameter"]),
                                new OracleParameter("parameterdisplay",rd2["ParameterDisplay"]),
                                new OracleParameter("value",value.Value),
                                new OracleParameter("timestamp",new DateTime(1970,1,1).AddSeconds(Convert.ToInt32(rd2["timestamp"])).AddHours(8)),
                                new OracleParameter("createtime",DateTime.Now),
                                new OracleParameter("unit",value.Unit),
                            };

                        oda.ExecuteCommand(
                            "insert into performance values(:id,:deviceid,:parameter,:parameterdisplay,:value,:timestamp,:createtime,:unit)",
                            param);
                        SysLog.WriteSysLog("One Performance Inserted.");
                    }
                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                }
            }
            GC.Collect();
        }

        //todo : convert performance value to value with unit
        static PerformanceValue ConvertValue(object parameter, object value)
        {
            if (parameter.ToString() == "MEMFREEMEM")
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "KB"
                };
            }
            else if (parameter.ToString() == "MEMORYAVAILABLE" || parameter.ToString() == "MEMMEMAVAILABLEBYTES")
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "MB"
                };
            }
            else
            {
                return new PerformanceValue
                {
                    Value = value.ToString(),
                    Unit = "%"
                };
            }
        }

        static void ImportBs(OracleDataAccess oda, string bppmStr, string key)
        {

            //read 业务系统可用性
            string sql1 = string.Empty;
            using (SAConnection conn = new SAConnection(bppmStr))
            {
                conn.Open();

                /////开始导入业务系统可用性
                //查询所有的report.t_tmartavailability
                if (hasTimeStamp)
                    sql1 = @"select project_name,value,fromtime,totime,createtime from report.t_tmartavailability where createtime > '" + timestamp + "'";
                else
                    sql1 = @"select project_name,value,fromtime,totime,createtime from report.t_tmartavailability";

                SACommand cmd = new SACommand(sql1, conn);
                SADataReader rd = null;
                List<BSAInSybase> bsAv = new List<BSAInSybase>();
                try
                {
                    rd = cmd.ExecuteReader();


                    while (rd.Read())
                    {
                        bsAv.Add(new BSAInSybase
                        {
                            BSName = rd["project_name"].ToString(),
                            Avg = rd["value"].ToString(),
                            FromTime = Convert.ToInt32(rd["fromtime"]),
                            ToTime = Convert.ToInt32(rd["totime"]),
                            CreateTime = Convert.ToDateTime(rd["createtime"])
                        });
                    }

                }
                catch (Exception ex)
                {
                    SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                }
                finally
                {
                    EnsureSACmdAndReader(cmd, rd);
                }

                foreach (var bs in bsAv)
                {
                    try
                    {
                        int bid = bsDic[bs.BSName + key];
                        //todo change the datetime format in oracle
                        string selectsql = string.Format("select id from businessavailable where createtime = to_date('{0}','mm/dd/yyyy hh24:mi:ss') and businesssystemid = {1}", bs.CreateTime.ToString("MM/dd/yyyy HH:mm:ss", DateTimeFormatInfo.InvariantInfo), bid);
                        if (oda.ExecuteScalar(selectsql) == null)
                        {
                            string insertsql = string.Empty;
                            DateTime d = new DateTime(1970, 1, 1).AddSeconds(bs.FromTime).AddHours(8);
                            insertsql = "insert into businessavailable values(:id,:bid,:avg,:timestamp,:createtime)";

                            OracleParameter[] param = new OracleParameter[] { 
                                new OracleParameter("id",null),
                                new OracleParameter("bid",bid),
                                new OracleParameter("avg",Convert.ToDouble(bs.Avg)),
                                new OracleParameter("createtime",bs.CreateTime),
                                new OracleParameter("timestamp",d)
                            };


                            oda.ExecuteCommand(insertsql, param);

                            SysLog.WriteSysLog("One BusinessAva Inserted.");
                        }
                    }
                    catch (Exception ex)
                    {
                        SysLog.WriteSysLog(ex.ToString() +ex.StackTrace);
                        continue;
                    }

                }

            }

        }

        #endregion
        public static void EnsureSACmdAndReader(SACommand cmd, SADataReader reader)
        {
            if (reader != null) reader.Close();
            if (cmd != null) cmd.Dispose();
        }

        public static void EnsureORCmdAndReader(OracleCommand cmd, OracleDataReader reader)
        {
            if (reader != null) reader.Close();
            if (cmd != null) cmd.Dispose();
        }

        private static T XmlDeserialize<T>(string str) where T : class
        {
            XmlSerializer mySerializer = new XmlSerializer(typeof(T));
            StreamReader mem2 = new StreamReader(
                    new MemoryStream(System.Text.Encoding.Unicode.GetBytes(str)),
                    System.Text.Encoding.Unicode);

            return (T)mySerializer.Deserialize(mem2);
        }

        public static string FilterSQL(string strVAlue, long fromtime, long totime)
        {
            string str = "";
            str = strVAlue.Replace(":fromTime", fromtime.ToString());
            str = str.Replace(":toTime", totime.ToString());
            return str;
        }

    }

}
